package j.util.jdbc.simple.core;

import j.util.New;
import j.util.StringUtils;
import j.util.jdbc.simple.core.operations.SimpleJdbcUpdateOperations;
import j.util.jdbc.simple.core.support.SimpleJdbcUtils;
import j.util.jdbc.simple.core.support.SqlUtils;
import j.util.jdbc.simple.metadata.Column;
import j.util.jdbc.simple.metadata.TableMetaData;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public final class SimpleJdbcUpdate extends SimpleJdbcAbstract implements
        SimpleJdbcUpdateOperations {

	@Override
	public <T> int update(Connection connection,Class<T> clazz, Map<String, Object> updatedData) throws SQLException{
		boolean isAutoCommit = false;
        if(connection.getAutoCommit()){
            isAutoCommit = true;
            connection.setAutoCommit(false);
        }
        SQLData sd = getSqlData(clazz);
		if(StringUtils.isNullOrEmpty(sd.getTmd().getPkColumns())){
			throw new RuntimeException("update must specify entity's pkColumns.");
		}
		SQLForUpdate sqlUpdate = sd.getSqlUpdate();
		List<String> argsOrder = New.list(updatedData.size());
		log.debug("begin execute update with checked、compared old data.");
        //执行更新前验证
        if(!doChecked(sd.getTmd().getColumns(),sqlUpdate,connection,updatedData)){
            return -1;
        }
        PreparedStatement stmt = null;
        String sql = null;
        TableMetaData tmd = sd.getTmd();
        if(tmd.isTree()){
            log.debug("这是树表的更新，需要检测是否更新左右值（若父节点与新父节点不一致，则需要更新左右值）");
            //树表
            String newNodePid = String.valueOf(updatedData.get(tmd.getParentFieldName().toUpperCase())); //新节点的父ID
            //nodeMap为移动节点的信息
            Map<String,Object> nodeMap = queryForNodeMap(connection,
                    String.format("select %s,%s,%s,%s from %s where %s=?",
                            tmd.getPkColumns(),tmd.getParentFieldName(),tmd.getLeftFieldName(),tmd.getRightFieldName()
                            ,tmd.getTableName(),tmd.getPkColumns()),
                    String.valueOf(updatedData.get(tmd.getPkColumns())));//现节点的父ID
            String nodePid = (String)nodeMap.get("pid");//更新前的父ID
            List<Map<String,Object>> updatedNodesMap = null;
            if(null!=newNodePid && null!=nodePid && !newNodePid.equals(nodePid)){
                log.debug("父节点与新父节点不一致，需要更新左右值");
                //新节点的父ID与原来的父ID不同，需要更新左右值
                //获取移动后父节点的当前信息
                Map<String,Object> newParentNodeMap =  queryForNodeMap(connection,
                        String.format("select %s,%s,%s,%s from %s where %s=?",
                                tmd.getPkColumns(),tmd.getParentFieldName(),tmd.getLeftFieldName(),tmd.getRightFieldName()
                                ,tmd.getTableName(),tmd.getPkColumns()),
                        newNodePid);//现节点的父ID
                int mnLft=0,mnRgt=0;
                mnLft = (Integer)nodeMap.get("lft");   //这是当前节点的左右值
                mnRgt = (Integer)nodeMap.get("rgt");

                //这是移动后节点的父节点的左右值
                int newLft=0,newRgt = 0;
                newLft = (Integer)newParentNodeMap.get("lft");
                newRgt = (Integer)newParentNodeMap.get("rgt");

                if(newLft>mnLft && newRgt<mnRgt){
                    log.debug("不能将节点移动到当前节点的子节点下");
                    //这种情况是不允许的，即不能将指定节点移动到此节点的下级节点上
                    throw new SQLException("不能将节点移动到当前节点的子节点下！");
                }
                //计算待移动的节点的个数，含移动节点本身
                int movedNodes = (mnRgt-mnLft-1)/2+1;
                log.debug(String.format("本次需要移动%s个节点，若大于1个节点，则需要先将这些节点临时保存到本地，最后再单独根据步长更新此批节点的左右值",movedNodes));
                if(movedNodes>1){
                    //待移动的节点有子节点，则先将此批节点保存到临时变量中，方便在更新完左右值时再更新此批待移动的节点的左右值
                    updatedNodesMap = New.list();
                    queryForNodesMap(connection,
                            String.format("select %s,%s,%s from %s where %s between ? and ?",
                                    tmd.getPkColumns(),tmd.getLeftFieldName(),tmd.getRightFieldName(),
                                    tmd.getTableName(),tmd.getLeftFieldName()),
                            updatedNodesMap,nodeMap.get("lft"),nodeMap.get("rgt"));
                }
                //更新左右值，需要分移动节点的右值与新父节点的右值之间的关系，若比父节点的右值小，则需要减，否则需要增加
                if(mnRgt<newRgt){
                    log.debug("移动的节点的右值比新父节点的右值小，那么凡移动节点的右值<右值<新父节点的右值，全部减去移动节点数*2；所有移动节点右值<新父节点的左值<=新父节点的右值，左值全部减去移动节点数*2");
                    //先更新右值
                    sql = String.format("update %s set %s=%s-? where %s>? and %s<?",
                            tmd.getTableName(),tmd.getRightFieldName(),tmd.getRightFieldName(),
                            tmd.getRightFieldName(),tmd.getRightFieldName());
                    execute(connection,sql,movedNodes*2,mnRgt,newRgt);
                    //再更新左值，所有左值>当前移动移动的右值并且左值<=新父节点的右值
                    sql = String.format("update %s set %s=%s-? where %s>? and %s<=?",
                            tmd.getTableName(),tmd.getLeftFieldName(),tmd.getLeftFieldName(),
                            tmd.getLeftFieldName(),tmd.getLeftFieldName());
                    execute(connection,sql,movedNodes*2,mnRgt,newRgt);
                    //最后，再更移动的节点的左右值
                    sql = String.format("update %s set %s=?,%s=? where %s=?",
                            tmd.getTableName(),tmd.getLeftFieldName(),tmd.getRightFieldName(),
                            tmd.getPkColumns());
                    if(updatedNodesMap==null){
                        //移动的仅有一个节点，即没有子节点
                        log.debug("本次移动只有一个节点，移动的节点的左值为新父节点的右值-2，右值为新父节点的右值减1");
                        execute(connection, sql, newRgt - 2, newRgt - 1, nodeMap.get("id"));
                    }else{
                        //移动的是一批节点，需要按照原来的上下级关系依次递减
                        int beginLft = newRgt-movedNodes*2;
                        int step = beginLft-mnLft;
                        log.debug(String.format("本次移动的是一批节点，步长计算方式为：新父节点的右值-移动节点数量*2-移动节点左值，本次的步长为：%s,临时保存的节点的左右值分别增加此步长。",step));
                        stmt = connection.prepareStatement(sql);
                        for(Map<String,Object> map : updatedNodesMap){
                            //设置左值参数
                            stmt.setInt(1, ((Integer) map.get("lft"))+step);
                            stmt.setInt(2, ((Integer) map.get("rgt"))+step);
                            stmt.setString(3,(String)map.get("id"));
                            stmt.addBatch();
                        }
                        stmt.executeBatch();
                    }
                }else{
                    //先更新右值，更新所有右值>=新父节点的右值且右值<移动节点的左值,满足这些条件的节点的节值增加移动节点数量*2；
                    log.debug("移动的节点的右值比新父节点的右值大，那么凡新父节点的右值<=右值<移动节点的左值，全部增加移动节点数*2；所有新父节点的右值<新父节点的左值<移动节点左值，左值全部增加移动节点数*2");
                    sql = String.format("update %s set %s=%s+? where %s>=? and %s<?",
                            tmd.getTableName(), tmd.getRightFieldName(), tmd.getRightFieldName(),
                            tmd.getRightFieldName(), tmd.getRightFieldName());
                    execute(connection, sql, movedNodes * 2, newRgt, mnLft);
                    //再更新左值，所有左值>新父节点的右值且左值<移动节点的右值，满足这些条件的节点的左值增加移动节点数量*2
                    sql = String.format("update %s set %s=%s+? where %s>? and %s<?",
                            tmd.getTableName(), tmd.getLeftFieldName(), tmd.getLeftFieldName(),
                            tmd.getLeftFieldName(), tmd.getLeftFieldName());
                    execute(connection, sql, movedNodes * 2, newRgt, mnLft);
                    //最后，再更移动的节点的左右值
                    sql = String.format("update %s set %s=?,%s=? where %s=?",
                            tmd.getTableName(), tmd.getLeftFieldName(), tmd.getRightFieldName(),
                            tmd.getPkColumns());
                    if (updatedNodesMap == null) {
                        //移动的仅有一个节点，即没有子节点
                        //左值为新父节点的右值，右值为新父节点的右值+1
                        log.debug("本次移动只有一个节点，移动的节点的左值为新父节点的右值，右值为新父节点的右值加1");
                        execute(connection, sql, newRgt, newRgt + 1, nodeMap.get("id"));
                    } else {
                        //移动的是一批节点，需要按照原来的上下级关系依次递减
                        int step = mnLft - newRgt;
                        log.debug(String.format("本次移动的是一批节点，步长计算方式为：移动节点的左值-新父节点的右值，本次的步长为：%s,临时保存的节点的左右值分别减去此步长。",step));
                        stmt = connection.prepareStatement(sql);
                        for (Map<String, Object> map : updatedNodesMap) {
                            //设置左值参数
                            stmt.setInt(1, ((Integer) map.get("lft")) - step);
                            stmt.setInt(2, ((Integer) map.get("rgt")) - step);
                            stmt.setString(3, (String) map.get("id"));
                            stmt.addBatch();
                        }
                        stmt.executeBatch();
                    }
                }
            }
        }
        if(sd.getTmd().getUpdateAttribute().isComparedToOld()){
            //对比新旧值，先获取旧值
            //Map<String, Object> args = Utils.toDbColumnKeyMap(sd.getTmd().getEntityAttributeColumnMap(), updatedData);
            sql = sqlUpdate.generateGetOldDataSql();
            log.debug("need compared to old data,then get old data sql:"+sql);
            Map<String,Object> old = SqlUtils.queryForMap(sd.getTmd().getColumns(), connection, sql, updatedData, sqlUpdate.getPkOrder());
            if(null == old || old.size() == 0){
                //没有从数据库中获取旧值，则无法完成更新操作
                log.debug("there's no data for current pk,or not to update.");
                return 0;
            }
            sql = sqlUpdate.generateUpdatedSqlWithComparedOld(updatedData, old, argsOrder);
            if(StringUtils.isNullOrEmpty(sql)){
                log.debug("no data to update.");
                return 0;
            }
        }else{
            //不用对比新旧值
            sql = sqlUpdate.generateUpdatedSqlByPk(updatedData, argsOrder);
            if(StringUtils.isNullOrEmpty(sql)){
                log.debug("no data to update.");
                return 0;
            }
        }
        log.debug("execute update sql:"+sql+", with value:"+updatedData);
        stmt = connection.prepareStatement(sql);
        SimpleJdbcUtils.setPreapredStatementParameters(sd.getTmd().getColumns(), stmt, argsOrder, updatedData);
        int ret = stmt.executeUpdate();
        stmt.close();
        if(isAutoCommit){
            connection.commit();
        }
        return ret;
	}

    private void execute(Connection conn,String sql,Object...args) throws SQLException{
        PreparedStatement stmt = conn.prepareStatement(sql);
        for(int i=0;i<args.length;i++){
            stmt.setObject(i+1,args[i]);
        }
        stmt.execute();
    }

    private Map<String,Object> queryForNodeMap(Connection conn,String sql,Object...args) throws SQLException{
        Map<String,Object> mapData = New.map();
        PreparedStatement pstmt = conn.prepareStatement(sql);
        if(null!=args){
            for(int i=0;i<args.length;i++){
                pstmt.setObject(i+1,args[i]);
            }
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                mapData.put("id",rs.getString(1));
                mapData.put("pid",rs.getString(2));
                mapData.put("lft",rs.getInt(3));
                mapData.put("rgt",rs.getInt(4));
            }
        }
        return mapData;
    }
    private void queryForNodesMap(Connection conn,String sql,List<Map<String,Object>> list,Object...args) throws SQLException{
        Map<String,Object> map;
        PreparedStatement pstmt = conn.prepareStatement(sql);
        if(null!=args){
            for(int i=0;i<args.length;i++){
                pstmt.setObject(i+1,args[i]);
            }
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                map = New.map();
                map.put("id",rs.getString(1));
                map.put("lft",rs.getInt(2));
                map.put("rgt",rs.getInt(3));
                list.add(map);
            }
        }
    }
	/**
	 * 执行更新前检测
	 * @param columns
	 * @param sqlUpdate
	 * @param connection
	 * @param args
	 * @return
	 * @throws java.sql.SQLException
	 */
	private boolean doChecked(Map<String, Column> columns,SQLForUpdate sqlUpdate,Connection connection,Map<String,Object> args) throws SQLException{
		String sql = sqlUpdate.getCheckedSql();
		if(StringUtils.isNullOrEmpty(sql)){
			//没有生成检查语句，则不检查，直接返回true
			log.debug("no checked.");
			return true;
		}
		log.debug("execute checked corredted:"+sql);
		List<String> checkedOrder = sqlUpdate.getCheckedOrder();
        PreparedStatement pstmt = connection.prepareStatement(sql);
        //设置参数
        SimpleJdbcUtils.setPreapredStatementParameters(columns, pstmt, checkedOrder, args);
        ResultSet rs = pstmt.executeQuery();
        int count = 0;
        if(rs.next()){
            count = rs.getInt(1);
            log.debug(count == 0 ?"checked ok.":"db has "+count+".");
        }
        pstmt.close();
        rs.close();
        return count==0;
	}

	@Override
	public <T> int[] updateBatch(Connection connection,Class<T> clazz, List<Map<String, Object>> list) throws SQLException{
		if(null==list||list.size()==0){
			throw new RuntimeException("no date to update.");
		}
		SQLData sd = getSqlData(clazz);
		if(StringUtils.isNullOrEmpty(sd.getTmd().getPkColumns())){
			throw new RuntimeException("update must specify entity's pkColumns.");
		}
		SQLForUpdate sqlUpdate = sd.getSqlUpdate();
		List<String> argsOrder = New.list();
		String sql = sqlUpdate.generateUpdatedSqlByPk(list.get(0), argsOrder);
		if(StringUtils.isNullOrEmpty(sql)){
			throw new RuntimeException("don't to generate updated sql.");
		}
		log.debug("execute update batch with sql:"+sql);
        PreparedStatement stmt = connection.prepareStatement(sql);
        for(Map<String,Object> args : list){
            //设置参数
            SimpleJdbcUtils.setPreapredStatementParameters(sd.getTmd().getColumns(),
                    stmt, argsOrder, args);
            stmt.addBatch();
            log.debug("add batched with value:"+args);
        }
        int[] ret = stmt.executeBatch();
        stmt.close();
        return ret;
	}

	@Override
	public <T> int update(Connection connection,Class<T> clazz, Map<String, Object> updatedData,
			Map<String, Object> where) throws SQLException{
		SQLData sd = getSqlData(clazz);
		SQLForUpdate sqlUpdate = sd.getSqlUpdate();
		List<String> argsOrder = New.list();
		String sql = sqlUpdate.generateUpdatedSqlByCW(updatedData, where, argsOrder);
		Map<String,Object> map = New.map();
		map.putAll(updatedData);
		map.putAll(where);
		log.debug("execute update by custom where with sql:"+sql+",with value:"+map);
        PreparedStatement stmt = connection.prepareStatement(sql);
        //设置参数
        SimpleJdbcUtils.setPreapredStatementParameters(sd.getTmd().getColumns(), stmt, argsOrder, map);
        int ret = stmt.executeUpdate();
        stmt.close();
        return ret;
	}

	@Override
	public <T> int updateNewByOldData(Connection connection,Class<T> clazz,
			Map<String, Object> newData, Map<String, Object> oldData) throws SQLException{
		SQLData sd = getSqlData(clazz);
		if(StringUtils.isNullOrEmpty(sd.getTmd().getPkColumns())){
			throw new RuntimeException("update must specify entity's pkColumns.");
		}
		SQLForUpdate sqlUpdate = sd.getSqlUpdate();
		List<String> argsOrder = New.list(newData.size());
		log.debug("begin execute update with checked、compared old data.");
        //执行更新前验证
        if(!doChecked(sd.getTmd().getColumns(),sqlUpdate,connection,newData)){
            return -1;
        }
        String sql = sqlUpdate.generateUpdatedSqlWithComparedOld(newData, oldData, argsOrder);
        log.debug("execute update sql:"+sql+", with value:"+newData);
        PreparedStatement stmt = connection.prepareStatement(sql);
        SimpleJdbcUtils.setPreapredStatementParameters(sd.getTmd().getColumns(), stmt, argsOrder, newData);
        int ret = stmt.executeUpdate();
        stmt.close();
        return ret;
	}
}
